﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using SmartProcess.Core.Commons.Tracing;

namespace MMSA.Services.SharedServices
{
    public static class MassiveUpsertExtensions
    {

        public static string Upsert(this DataTable table, string matchColumn, bool useIdentityInsert = false,
            string[] identityColumns = null,
            string[] excludedUpdateColumns = null)
        {
            TracingService tracing = new TracingService("MMSA.Services");
            string traceServicio = string.Empty;
            try
            {
                var conection = ConfigurationManager.AppSettings["conection"];
                SqlConnection conn = new SqlConnection(conection);
                var query = new StringBuilder();
                query.Append("SELECT TOP 0 * INTO #temp_" + table.TableName + " FROM " + table.TableName);

                traceServicio = "Query a realizarse: " + query.ToString();

                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = "#temp_" + table.TableName;
                bulkCopy.WriteToServer(table);


                var updateString = string.Empty;
                var insertString = string.Empty;
                var insertValuesString = string.Empty;

                var keys = table.PrimaryKey.Select(c => c.ColumnName).ToList();

                if (identityColumns != null)
                {
                    foreach (var removedKeys in identityColumns)
                    {
                        keys.Remove(removedKeys);
                    }
                }

                if (excludedUpdateColumns == null)
                    excludedUpdateColumns = new string[0];


                traceServicio = "Antes de la creación de cada sentencia";

                foreach (DataColumn item in table.Columns)
                {
                    Boolean flag1 = true;



                    if (!keys.Contains(item.ColumnName) && flag1)
                    {
                        if (!excludedUpdateColumns.Contains(item.ColumnName))
                        {
                            updateString += string.Format(" D.[{0}] = S.[{0}] ,", item.ColumnName);
                        }
                        insertString += string.Format("[{0}],", item.ColumnName);

                        insertValuesString += string.Format("S.[{0}], ", item.ColumnName);

                    }
                    if (table.TableName.Equals("PED_PRODUCTO"))
                    {
                        if (keys.Contains(item.ColumnName))
                        {
                            insertString += string.Format("[{0}],", item.ColumnName);
                            insertValuesString += string.Format("S.[{0}], ", item.ColumnName);

                        }
                    }


                }

                Boolean flag = true;
                traceServicio = "antes del merge";
                var mergeString = "";
                if (table.TableName.Equals("PED_LISTA_PRECIOS"))
                {

                    mergeString = "MERGE " + table.TableName + " AS D " +
                                  " USING " + bulkCopy.DestinationTableName + " AS S " +
                                  " ON D." + matchColumn + " = S." + matchColumn +
                                  " and D.lst_id=S.lst_id " + " WHEN MATCHED " +
                                  " THEN " +
                                              "UPDATE SET " +
                                              updateString.Substring(0, updateString.Length - 1) +
                                  " WHEN NOT MATCHED " +
                                  " THEN " +
                                  " INSERT (" + insertString.Substring(0, insertString.Length - 1) + ") " +
                                  " VALUES (" + insertValuesString.Substring(0, insertValuesString.Length - 2) + ");";


                }
                else
                {
                    mergeString = "MERGE " + table.TableName + " AS D " +
                                  " USING " + bulkCopy.DestinationTableName + " AS S " +
                                  " ON D." + matchColumn + " = S." + matchColumn +
                                  " WHEN MATCHED " +
                                  " THEN " +
                                              "UPDATE SET " +
                                              updateString.Substring(0, updateString.Length - 1) +
                                  " WHEN NOT MATCHED " +
                                  " THEN " +
                                  " INSERT (" + insertString.Substring(0, insertString.Length - 1) + ") " +
                                  " VALUES (" + insertValuesString.Substring(0, insertValuesString.Length - 2) + ");";
                    //}

                }

                traceServicio = "Query Merge a ejecutarse: " + mergeString;
                if (flag)
                {
                   if (useIdentityInsert)
                    {
                        var identityCommand= "SET IDENTITY_INSERT " + table.TableName + " ON  ";
                        var command = new SqlCommand(identityCommand, conn);
                        command.ExecuteNonQuery();
                    }
                        
                    var mergeCommand = new SqlCommand(mergeString, conn);

                    var affectedRows = mergeCommand.ExecuteNonQuery();
                    if (useIdentityInsert)
                    {
                        var identityCommand= "SET IDENTITY_INSERT " + table.TableName + " OFF ";
                        var command = new SqlCommand(identityCommand, conn);
                        command.ExecuteNonQuery();
                    }

                }
                conn.Close();
                return bulkCopy.DestinationTableName;
            }
            catch (Exception exc)
            {
                if (exc != null)
                    tracing.ErrorFormat("MMSA.Services.SharedServices.MassiveUpsertExtensions ==> TRACE:{0}, ==> ERROR:{1}, ==> STACKTRACE:{2}", traceServicio, exc.Message, exc.StackTrace);
                throw;// poner mas error de la fk
            }

        }
    }
}
